﻿using System;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Windows.Forms;

namespace ChaXun
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void ExportExcels(string fileName, DataGridView myDGV) //导出EXCEL
        {
            string saveFileName = "";
            SaveFileDialog saveFileDialog = new SaveFileDialog
            {
                DefaultExt = "xlsx",
                Filter = "Excel文件|*.xlsx",
                FileName = fileName
            };
            SaveFileDialog saveDialog = saveFileDialog;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            progressBar1.Maximum = myDGV.RowCount + 1;//进度条
            progressBar1.Value = 1;//进度条
            progressBar1.Visible = true;//进度条
            label9.Visible = true;//进度条
            label8.Visible = false;
            //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
            }
            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                progressBar1.Value += 1;//进度条
                double d = progressBar1.Value / (double)progressBar1.Maximum;//进度条
                System.Math.Round(d, 2);//进度条
                label9.Text = d.ToString("##%"); ;//进度条
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            xlApp.Quit();
            GC.Collect();//强行销毁
            MessageBox.Show("文件保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            progressBar1.Visible = false;//进度条
            label9.Visible = false;//进度条
            label8.Visible = true;
            label8.Text = "导出成功";
        }

        private void Form1_Load(object sender, EventArgs e) //窗体启动加载内容
        {

        }

        private void Button8_Click(object sender, EventArgs e) //退出程序
        {
            Environment.Exit(0);
        }

        private void Button15_Click(object sender, EventArgs e) //病史查询
        {
            DataTable dt = new DataTable();
            string strConn = "user id=tyyl;password=90tyyl130529;data source=tyyl;";
            OracleConnection con = new OracleConnection(strConn);
            try
            {
                string date1;
                string date2;
                string sqlcx1;
                date1 = dateTimePicker1.Value.ToString();
                date2 = dateTimePicker2.Value.ToString();
                Convert.ToDateTime(date1);
                Convert.ToDateTime(date2);
                sqlcx1 = "select jj_comm_jc_result.cid as CID,jj_comm_jc_result.field_comment as bs,jj_comm_jc_result.field_results as bsms from jj_comm_jc_result inner join temp_select on (jj_comm_jc_result.cid = temp_select.cid） where jj_comm_jc_result.table_id = '0434' and jj_comm_jc_result.op_datetime between to_date('" + date1 + "', 'yyyy-MM-dd hh24:mi:ss') and to_date('" + date2 + "', 'yyyy-MM-dd hh24:mi:ss')";
                //------分割线-------
                con.Open();
                string sqlstr = sqlcx1;
                OracleCommand cmd = new OracleCommand(sqlstr, con);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(dt);
                cmd.Dispose();
                con.Dispose();
                dataGridView1.DataSource = dt;
                //return dt;
                label8.Visible = true;
                label8.Text = "查询成功！";
            }
            catch
            {
                label8.Visible = true;
                label8.Text = "查询失败！";
            }
            finally
            {
                con.Close();
            }
        }

        private void Button10_Click(object sender, EventArgs e) //执行导出
        {
            string a = "";
            ExportExcels(a, dataGridView1);
        }

        private void Button7_Click(object sender, EventArgs e) //基本信息查询
        {
            DataTable dt = new DataTable();
            string strConn = "user id=tyyl;password=90tyyl130529;data source=tyyl;";
            OracleConnection con = new OracleConnection(strConn);
            try
            {
                string date1;
                string date2;
                string sqlcx1;
                date1 = dateTimePicker1.Value.ToString();
                date2 = dateTimePicker2.Value.ToString();
                Convert.ToDateTime(date1);
                Convert.ToDateTime(date2);
                sqlcx1 = "select a.cid as cid,b.tjsj as tjsj,（case a.zzhs when '90' then '美年健康奥亚分院' else '美年健康金茂分院' end）tjyy,a.xm,（case a.xb when '0' then '女' else '男' end） xb,a.mz as mz,(case a.hyzk when '1' then '已婚' else '未婚' end) hyzk,a.csrq as csrq,a.dwmc as bm,a.sfzhm as sfzhm,a.lxdh as dianhua1,a.yddh as dianhua2,a.dwdm as dwdm,b.temp_bz as tjtc,c.field_results as zjjl from bas_cust_infor a, jj_yyqkb b, jj_comm_jc_result c where a.cid = b.cid and b.vid = c.vid and c.table_id = '2301' and c.item_id = '022' and a.dwdm = '201014872' and b.qtdjsj between to_date('"+date1+"', 'yyyy-MM-dd hh24:mi:ss') and to_date('"+date2+"', 'yyyy-MM-dd hh24:mi:ss')";
                //------分割线-------
                con.Open();
                string sqlstr = sqlcx1;
                OracleCommand cmd = new OracleCommand(sqlstr, con);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(dt);
                cmd.Dispose();
                con.Dispose();
                dataGridView1.DataSource = dt;
                //return dt;
                label8.Visible = true;
                label8.Text = "查询成功！";
            }
            catch
            {
                label8.Visible = true;
                label8.Text = "查询失败！";
            }
            finally
            {
                con.Close();
            }
        }

        private void Button16_Click(object sender, EventArgs e) //回访记录
        {
            DataTable dt = new DataTable();
            string strConn = "user id=tyyl;password=90tyyl130529;data source=tyyl;";
            OracleConnection con = new OracleConnection(strConn);
            try
            {
                string date1;
                string date2;
                string sqlcx1;
                date1 = dateTimePicker1.Value.ToString();
                date2 = dateTimePicker2.Value.ToString();
                Convert.ToDateTime(date1);
                Convert.ToDateTime(date2);
                sqlcx1 = "select /*jj_gwyc_list2019.cid as 健检号CID,*/jj_gwyc_list2019.vid as ID号,jj_gwyc_list2019.xm as 姓名,(case jj_gwyc_list2019.xb when '0' then '女' else '男' end) 性别,jj_gwyc_list2019.bz4 as 出生日期,bas_cust_infor.lxdh as 电话1,bas_cust_infor.yddh as 电话2,/*bas_cust_infor.user_password as 网查密码,*/bas_ent_info.dwmc as 单位名称,jj_gwyc_list2019.yysj as 体检时间,jj_gwyc_list2019.lr_date as 阳性上报时间,(case jj_gwyc_list2019.in_factory when '90' then '奥亚分院' when 'X2' then '金城分院' else '金茂分院' end) 体检门店,(case jj_gwyc_list2019.bz2 when 'Y' then '危急值' else '重大阳性' end) as 阳性级别,jj_gwyc_list2019.mc as 阳性名称,jj_gwyc_list2019.mc_detail as 阳性内容,jj_gwyc_list2019.lr_opername as 上报人员,jj_gwyc_list2019.bz1 as 医师回访,jj_gwyc_list2019.bz5 as 医师签字 from jj_gwyc_list2019 inner join bas_cust_infor on jj_gwyc_list2019.cid = bas_cust_infor.cid inner join bas_ent_info on bas_cust_infor.dwdm = bas_ent_info.dwdm where jj_gwyc_list2019.sh_date between to_date('" + date1 + "', 'yyyy-MM-dd hh24:mi:ss') and to_date('" + date2 + "', 'yyyy-MM-dd hh24:mi:ss') order by jj_gwyc_list2019.lr_date desc";
                //------分割线-------
                con.Open();
                string sqlstr = sqlcx1;
                OracleCommand cmd = new OracleCommand(sqlstr, con);
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(dt);
                cmd.Dispose();
                con.Dispose();
                dataGridView1.DataSource = dt;
                //return dt;
                label8.Visible = true;
                label8.Text = "查询成功！";
            }
            catch
            {
                label8.Visible = true;
                label8.Text = "查询失败！";
            }
            finally
            {
                con.Close();
            }
        }
    }
}
